package com.childenglish.mapper;

import com.childenglish.entity.PictureBook;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface PictureBookMapper {

    /**
     * 根据绘本ID查询绘本
     */
    @Select("SELECT * FROM picture_book WHERE book_id = #{bookId}")
    @Results({
            @Result(property = "bookId", column = "book_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "author", column = "author"),
            @Result(property = "coverUrl", column = "cover_url"),
            @Result(property = "difficulty", column = "difficulty"),
            @Result(property = "ageMin", column = "age_min"),
            @Result(property = "ageMax", column = "age_max"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "description", column = "description"),
            @Result(property = "totalPages", column = "total_pages"),
            @Result(property = "content", column = "content"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    PictureBook findById(@Param("bookId") Long bookId);

    /**
     * 根据难度等级查询绘本列表
     */
    @Select("SELECT * FROM picture_book WHERE difficulty = #{difficulty} AND status = '0' ORDER BY create_time DESC")
    @Results({
            @Result(property = "bookId", column = "book_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "author", column = "author"),
            @Result(property = "coverUrl", column = "cover_url"),
            @Result(property = "difficulty", column = "difficulty"),
            @Result(property = "ageMin", column = "age_min"),
            @Result(property = "ageMax", column = "age_max"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "description", column = "description"),
            @Result(property = "totalPages", column = "total_pages"),
            @Result(property = "content", column = "content"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<PictureBook> findByDifficulty(@Param("difficulty") String difficulty);

    /**
     * 根据年龄范围查询绘本列表
     */
    @Select("SELECT * FROM picture_book WHERE age_min <= #{age} AND age_max >= #{age} AND status = '0' ORDER BY difficulty, create_time DESC")
    @Results({
            @Result(property = "bookId", column = "book_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "author", column = "author"),
            @Result(property = "coverUrl", column = "cover_url"),
            @Result(property = "difficulty", column = "difficulty"),
            @Result(property = "ageMin", column = "age_min"),
            @Result(property = "ageMax", column = "age_max"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "description", column = "description"),
            @Result(property = "totalPages", column = "total_pages"),
            @Result(property = "content", column = "content"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<PictureBook> findByAge(@Param("age") Integer age);

    /**
     * 根据年级级别查询绘本列表
     */
    @Select("SELECT * FROM picture_book WHERE grade_level = #{gradeLevel} AND status = '0' ORDER BY difficulty, create_time DESC")
    @Results({
            @Result(property = "bookId", column = "book_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "author", column = "author"),
            @Result(property = "coverUrl", column = "cover_url"),
            @Result(property = "difficulty", column = "difficulty"),
            @Result(property = "ageMin", column = "age_min"),
            @Result(property = "ageMax", column = "age_max"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "description", column = "description"),
            @Result(property = "totalPages", column = "total_pages"),
            @Result(property = "content", column = "content"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<PictureBook> findByGradeLevel(@Param("gradeLevel") String gradeLevel);

    /**
     * 根据班级类别查询绘本列表
     */
    @Select("SELECT * FROM picture_book WHERE class_category = #{classCategory} AND status = '0' ORDER BY difficulty, create_time DESC")
    @Results({
            @Result(property = "bookId", column = "book_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "author", column = "author"),
            @Result(property = "coverUrl", column = "cover_url"),
            @Result(property = "difficulty", column = "difficulty"),
            @Result(property = "ageMin", column = "age_min"),
            @Result(property = "ageMax", column = "age_max"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "description", column = "description"),
            @Result(property = "totalPages", column = "total_pages"),
            @Result(property = "content", column = "content"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<PictureBook> findByClassCategory(@Param("classCategory") String classCategory);

    /**
     * 综合查询：根据年龄、年级级别和班级类别推荐绘本
     */
    @Select("<script>" +
            "SELECT * FROM picture_book WHERE status = '0' " +
            "<if test='age != null'> AND age_min &lt;= #{age} AND age_max &gt;= #{age} </if>" +
            "<if test='gradeLevel != null and gradeLevel != \"\"'> AND (grade_level = #{gradeLevel} OR grade_level IS NULL) </if>" +
            "<if test='classCategory != null and classCategory != \"\"'> AND (class_category = #{classCategory} OR class_category IS NULL) </if>" +
            "ORDER BY difficulty ASC, create_time DESC" +
            "</script>")
    @Results({
            @Result(property = "bookId", column = "book_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "author", column = "author"),
            @Result(property = "coverUrl", column = "cover_url"),
            @Result(property = "difficulty", column = "difficulty"),
            @Result(property = "ageMin", column = "age_min"),
            @Result(property = "ageMax", column = "age_max"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "description", column = "description"),
            @Result(property = "totalPages", column = "total_pages"),
            @Result(property = "content", column = "content"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<PictureBook> findRecommendedBooks(@Param("age") Integer age, 
                                           @Param("gradeLevel") String gradeLevel, 
                                           @Param("classCategory") String classCategory);

    /**
     * 查询所有正常状态的绘本
     */
    @Select("SELECT * FROM picture_book WHERE status = '0' ORDER BY create_time DESC")
    @Results({
            @Result(property = "bookId", column = "book_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "author", column = "author"),
            @Result(property = "coverUrl", column = "cover_url"),
            @Result(property = "difficulty", column = "difficulty"),
            @Result(property = "ageMin", column = "age_min"),
            @Result(property = "ageMax", column = "age_max"),
            @Result(property = "gradeLevel", column = "grade_level"),
            @Result(property = "classCategory", column = "class_category"),
            @Result(property = "description", column = "description"),
            @Result(property = "totalPages", column = "total_pages"),
            @Result(property = "content", column = "content"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<PictureBook> findAllActive();

    /**
     * 插入新绘本
     */
    @Insert("INSERT INTO picture_book (title, author, cover_url, difficulty, age_min, age_max, grade_level, " +
            "class_category, description, total_pages, content, status, create_time, update_time) " +
            "VALUES (#{title}, #{author}, #{coverUrl}, #{difficulty}, #{ageMin}, #{ageMax}, #{gradeLevel}, " +
            "#{classCategory}, #{description}, #{totalPages}, #{content}, #{status}, #{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "bookId")
    int insert(PictureBook pictureBook);

    /**
     * 更新绘本信息
     */
    @Update("UPDATE picture_book SET title = #{title}, author = #{author}, cover_url = #{coverUrl}, " +
            "difficulty = #{difficulty}, age_min = #{ageMin}, age_max = #{ageMax}, grade_level = #{gradeLevel}, " +
            "class_category = #{classCategory}, description = #{description}, total_pages = #{totalPages}, " +
            "content = #{content}, status = #{status}, update_time = #{updateTime} WHERE book_id = #{bookId}")
    int update(PictureBook pictureBook);
}

